//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR05
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <code>Broker</code> for <code>DeliveryBean</code>.
 */
public class DeliveryBroker extends Broker<DeliveryBean> {	
	
	/* Attributes */
	
	private int newId;
	
	/* END Attributes */
	
	
	/* Singleton Constructor */
	
	private static DeliveryBroker deliveryBroker;

	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private DeliveryBroker () throws ErrorBean {
		insertSQL =
		updateSQL =
		deleteDeliverySQL = null;
	}
	
	/**
	 * Retrieves the static DeliveryBroker instance.
	 * @return static DeliveryBroker
	 * @throws ErrorBean
	 */
	public synchronized static DeliveryBroker getBroker () throws ErrorBean {
		if (deliveryBroker == null)
			deliveryBroker = new DeliveryBroker();
		return deliveryBroker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement insertSQL;
	private PreparedStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "INSERT INTO delivery SET delivery_status = ?, first_name = ?, last_name = ?, address = ?, city = ?, province = ?, postal_code = ?, phone = ?, delivery_instructions = ?, dwelling_type = ?, building_name = ?, room_number = ?;");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE delivery SET delivery_status = ?, first_name = ?, last_name = ?, address = ?, city = ?, province = ?, postal_code = ?, phone = ?, delivery_instructions = ?, dwelling_type = ?, building_name = ?, room_number = ? WHERE delivery_id = ?;");
	}
	
	private PreparedStatement deleteDeliverySQL;
	private PreparedStatement getDeleteItemSQL () throws ErrorBean {
		return prepare(deleteDeliverySQL, "DELETE FROM delivery WHERE delivery_id = ?;");
	}
	
	/* END Prepared Statements */	
	
	
	/* Operational Methods */
	
	@Override
	public List<DeliveryBean> listAll () throws ErrorBean {
		List<DeliveryBean> search = new ArrayList<DeliveryBean>();
		DeliveryBean db;
		
		StringBuilder sql = new StringBuilder("SELECT * FROM delivery;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					db = new DeliveryBean();
					db.setId(results.getInt("delivery_id"));
					db.setFirstname(results.getString("first_name"));
					db.setLastname(results.getString("last_name"));
					db.setPhone(results.getString("phone"));
					db.setAddress(results.getString("address"));
					db.setCity(results.getString("city"));
					db.setProvince(results.getString("province"));
					db.setPostalCode(results.getString("postal_code"));
					db.setDwellingType(results.getString("dwelling_type"));
					db.setRoomNo(results.getString("room_number"));
					db.setBuildingName(results.getString("building_name"));
					db.setDeliveryInstruction(results.getString("delivery_instructions"));
					db.setDeliveryStatus(results.getString("delivery_status"));
					
					search.add(db);
				}
			}
			return search;
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
			return null;
		}
	}

	@Override
	public boolean persist(DeliveryBean db) throws ErrorBean {
		db.isValid();
		
		int delivery_id = db.getId();
		String first_name = db.getFirstname();
		String last_name = db.getLastname();
		String phone = db.getPhone();
		String address = db.getAddress();
		String city = db.getCity();
		String province = db.getProvince();
		String postal_code = db.getPostalCode();
		String dwelling_type = db.getDwellingType();
		String room_number = db.getRoomNo();
		String building_name = db.getBuildingName();
		String delivery_instructions = db.getDeliveryInstruction();
		String delivery_status = db.getDeliveryStatus();

		if (delivery_id == 0) {
			try {
				PreparedStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, delivery_status);
				insertSQL.setString(2, first_name);
				insertSQL.setString(3, last_name);
				insertSQL.setString(4, address);
				insertSQL.setString(5, city);
				insertSQL.setString(6, province);
				insertSQL.setString(7, postal_code);
				insertSQL.setString(8, phone);
				insertSQL.setString(9, delivery_instructions);
				insertSQL.setString(10, dwelling_type);
				insertSQL.setString(11, building_name);
				insertSQL.setString(12, room_number);
				insertSQL.execute();
				// get newly created delivery ID
				DeliveryBean newDelivery = deliveryBroker.search(db).get(0);
				newId = newDelivery.getId(); 
				System.out.println("New del ID:"+ newId);
				return true;
			} catch (SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (delivery_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, delivery_status);
				updateSQL.setString(2, first_name);
				updateSQL.setString(3, last_name);
				updateSQL.setString(4, address);
				updateSQL.setString(5, city);
				updateSQL.setString(6, province);
				updateSQL.setString(7, postal_code);
				updateSQL.setString(8, phone);
				updateSQL.setString(9, delivery_instructions);
				updateSQL.setString(10, dwelling_type);
				updateSQL.setString(11, building_name);
				updateSQL.setString(12, room_number);
				updateSQL.setInt(13, delivery_id);
				updateSQL.execute();
				return true;
			} catch (SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public boolean remove (DeliveryBean db) throws ErrorBean {
		int delivery_id = db.getId();
		
		try {
			PreparedStatement deleteItemSQL = getDeleteItemSQL();
			deleteItemSQL.setInt(1, delivery_id);
			deleteItemSQL.execute();
			
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}

	@Override
	public List<DeliveryBean> search (DeliveryBean searchBean) throws ErrorBean {
		List<DeliveryBean> search = new ArrayList<DeliveryBean>();
		DeliveryBean db;
		
		boolean isFirst = true;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM delivery");
		
		//Search by id
		int id = searchBean.getId();
		if (id > 0)
			sql.append(appendClause("delivery_id = " + id, isFirst));
		else {
		
			//Search by first name
			String first_name = searchBean.getFirstname();
			if (!"".equals(first_name) && first_name != null) {
				sql.append(appendClause("first_name LIKE '%" + first_name + "%'", isFirst));
				isFirst = false;
			}
			
			//Search by last name
			String last_name = searchBean.getLastname();
			if (!"".equals(last_name) && last_name != null) {
				sql.append(appendClause("last_name LIKE '%" + last_name + "%'", isFirst));
				isFirst = false;
			}
			
			//Search by phone number
			String phone = searchBean.getPhone();
			if (!"".equals(phone) && phone != null) {
				sql.append(appendClause("phone LIKE '%" + phone + "%'", isFirst));
				isFirst = false;
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					db = new DeliveryBean();
					db.setId(results.getInt("delivery_id"));
					db.setFirstname(results.getString("first_name"));
					db.setLastname(results.getString("last_name"));
					db.setPhone(results.getString("phone"));
					db.setAddress(results.getString("address"));
					db.setCity(results.getString("city"));
					db.setProvince(results.getString("province"));
					db.setPostalCode(results.getString("postal_code"));
					db.setDwellingType(results.getString("dwelling_type"));
					db.setRoomNo(results.getString("room_number"));
					db.setBuildingName(results.getString("building_name"));
					db.setDeliveryInstruction(results.getString("delivery_instructions"));
					db.setDeliveryStatus(results.getString("delivery_status"));
					
					search.add(db);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Methods */
	
	/**
	 * Returns the newest ID.
	 * @return the new ID
	 */
	public int getNewId () {
		return newId;
	}
	
	/* END Methods */
	
}
